package com.dao.imp;

import com.dao.ResumeDao;
import com.entity.Job;
import com.entity.Resume;
import com.mysql.cj.util.StringUtils;
import com.utils.DButil;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ResumeDaoImpl implements ResumeDao {
    @Override
    public List<Object> selectResumeListByCompanyId(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String pageStr = req.getParameter("page");
        System.out.println("pageStr = " + pageStr);
        String limitStr = req.getParameter("limit");
        System.out.println("limitStr = " + limitStr);
        String keyCreateTime = req.getParameter("keyCreateTime");
        String keyEndTime = req.getParameter("keyEndTime");
        String keyState = req.getParameter("keyState");
        String companyId = req.getParameter("companyId");
        System.out.println("companyId = " + companyId);
        String jobId = req.getParameter("jobId");
        System.out.println("jobId = " + jobId);
        int page1 = Integer.parseInt(pageStr);
        int limit1 = Integer.parseInt(limitStr);
        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyState)
                && StringUtils.isNullOrEmpty(keyCreateTime) && StringUtils.isNullOrEmpty(keyEndTime)
        ) {
            sql = "SELECT r_userinfo.userid,r_userinfo.uname,auditid,r_job.jobname,xueli,r_userinfo.zhuanye,salary,jobtype,r_resume.createtime,r_job.jobid,auditstate,r_resume.description,src,opinion from r_userinfo,r_resume,r_job where r_userinfo.userid=r_resume.userid  and r_job.jobid=r_resume.jobid and r_job.companyid=? and r_job.jobid=?  limit ?,?";
//            sql = "SELECT * from r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户' and r_job.companyid=? and (jobstate='审核通过' OR jobstate='已发布' OR jobstate='已撤销发布' ) limit ?,?";
            ps = conn.prepareStatement(sql);
            ps.setInt(3, (page1 - 1) * limit1);
            ps.setInt(4, limit1);
            ps.setString(1,companyId);
            ps.setString(2,jobId);
            System.out.println("sql = " + sql);
        } else if (!StringUtils.isNullOrEmpty(keyState)||
                !StringUtils.isNullOrEmpty(keyCreateTime)||
                !StringUtils.isNullOrEmpty(keyEndTime)
        ) {
            sql = "  SELECT r_userinfo.userid,r_userinfo.uname,auditid,r_job.jobname,xueli,r_userinfo.zhuanye,salary,jobtype,r_resume.createtime,r_job.jobid,auditstate,r_resume.description,src,opinion from r_userinfo,r_resume,r_job where r_userinfo.userid=r_resume.userid  and r_job.jobid=r_resume.jobid and r_job.companyid=? and r_job.jobid=?   and auditstate like ?  and r_resume.createtime >=?  and r_resume.createtime  <= ?  limit ?,?";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);
            ps.setInt(6, (page1 - 1) * limit1);
            ps.setInt(7, limit1);
            ps.setString(1,companyId);
            ps.setString(2,jobId);
            ps.setString(3, "%"+keyState+"%");
            ps.setString(4, keyCreateTime);
            ps.setString(5, keyEndTime);
            System.out.println("ps = " + ps);
        }
        ResultSet rs = ps.executeQuery();
        List<Object> users = new ArrayList<Object>();
        while (rs.next()) {
            Resume resume = new Resume();
            resume.setUserId(rs.getString("userid"));
            resume.setUserName(rs.getString("uname"));
            resume.setAuditId(rs.getInt("auditid"));
            resume.setJobName(rs.getString("jobname"));
            resume.setXueLi(rs.getString("xueli"));
            resume.setMajor(rs.getString("zhuanye"));
            resume.setSalary(rs.getString("salary"));
            resume.setJobType(rs.getString("jobtype"));
            resume.setAddTime(rs.getString("createtime"));
            resume.setJobId(rs.getInt("jobid"));
            resume.setAuditState(rs.getString("auditstate"));
            resume.setDescription(rs.getString("description"));
            resume.setOpinion(rs.getString("opinion"));
            resume.setSrc(rs.getString("src"));
            users.add(resume);
        }
        return users;
    }

    @Override
    public List<Object> selectDeliverdResume(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String pageStr = req.getParameter("page");
        System.out.println("pageStr = " + pageStr);
        String limitStr = req.getParameter("limit");
        System.out.println("limitStr = " + limitStr);
        String keyCreateTime = req.getParameter("keyCreateTime");
        String keyEndTime = req.getParameter("keyEndTime");
        String keyState = req.getParameter("keyState");
        String userId = req.getParameter("userId");
        String companyName = req.getParameter("companyName");
        String jobName = req.getParameter("jobName");
        System.out.println("userId = " + userId);
//        String jobId = req.getParameter("jobId");
        int page1 = Integer.parseInt(pageStr);
        int limit1 = Integer.parseInt(limitStr);
        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyState)
                && StringUtils.isNullOrEmpty(keyCreateTime) && StringUtils.isNullOrEmpty(keyEndTime)
                && StringUtils.isNullOrEmpty(companyName) && StringUtils.isNullOrEmpty(jobName)) {
            sql = "SELECT r_resume.*,r_company.`name`,r_job.jobname,r_job.jobdescription,r_job.jobtype,salary,jobstate from r_company,r_resume,r_job where r_job.jobid=r_resume.jobid and r_resume.userid=? and r_company.companyid=r_job.companyid   limit ?,?";
            ps = conn.prepareStatement(sql);
            ps.setInt(2, (page1 - 1) * limit1);
            ps.setInt(3, limit1);
            ps.setString(1,userId);
//            ps.setString(2,jobId);
            System.out.println("sql = " + sql);
        } else if (!StringUtils.isNullOrEmpty(keyState)||
                !StringUtils.isNullOrEmpty(keyCreateTime)||
                !StringUtils.isNullOrEmpty(keyEndTime) ||
                !StringUtils.isNullOrEmpty(companyName) ||
                !StringUtils.isNullOrEmpty(jobName)
        ) {
            sql = " SELECT r_resume.*,r_company.`name`,r_job.jobname,r_job.jobdescription,r_job.jobtype,salary,jobstate from r_company,r_resume,r_job where r_job.jobid=r_resume.jobid and r_resume.userid=? and r_company.companyid=r_job.companyid   and auditstate like ?  and r_resume.createtime >=?  and r_resume.createtime  <= ? and r_company.name like ? and r_job.jobname like ? limit ?,?";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);
            ps.setInt(7, (page1 - 1) * limit1);
            ps.setInt(8, limit1);
            ps.setString(1,userId);
//            ps.setString(2,jobId);
            ps.setString(2, "%"+keyState+"%");
            ps.setString(5, "%"+companyName+"%");
            ps.setString(6, "%"+jobName+"%");
            ps.setString(3, keyCreateTime);
            ps.setString(4, keyEndTime);

            System.out.println("ps = " + ps);
        }
        ResultSet rs = ps.executeQuery();
        List<Object> users = new ArrayList<Object>();
        while (rs.next()) {
            Resume resume = new Resume();
            resume.setUserId(rs.getString("userid"));
            resume.setAuditId(rs.getInt("auditid"));
            resume.setJobName(rs.getString("jobname"));
            resume.setSalary(rs.getString("salary"));
            resume.setJobState(rs.getString("jobstate"));
            resume.setJobType(rs.getString("jobtype"));
            resume.setAddTime(rs.getString("createtime"));
            resume.setJobId(rs.getInt("jobid"));
            resume.setAuditState(rs.getString("auditstate"));
            resume.setDescription(rs.getString("description"));
            resume.setOpinion(rs.getString("opinion"));
            resume.setReadState(Integer.parseInt(rs.getString("readstate")));
            resume.setCompanyName("name");
            resume.setSrc(rs.getString("src"));
            users.add(resume);
        }
        return users;
    }

    @Override
    public int countResumeBycompanyId(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String keyCreateTime = req.getParameter("keyCreateTime");
        String keyEndTime = req.getParameter("keyEndTime");
        String keyState = req.getParameter("keyState");
        String companyId = req.getParameter("companyId");
        String jobId = req.getParameter("jobId");

        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyState)
                && StringUtils.isNullOrEmpty(keyCreateTime) && StringUtils.isNullOrEmpty(keyEndTime)
               ) {
            sql = "SELECT count(*) as sum from r_userinfo,r_resume,r_job where r_userinfo.userid=r_resume.userid  and r_job.jobid=r_resume.jobid and r_job.companyid=? and r_job.jobid=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,companyId);
            ps.setString(2,jobId);
            System.out.println("sql = " + sql);
        } else if (
                !StringUtils.isNullOrEmpty(keyState)||
                !StringUtils.isNullOrEmpty(keyCreateTime)||
                !StringUtils.isNullOrEmpty(keyEndTime)
        ) {
            sql = "SELECT count(*) as sum from r_userinfo,r_resume,r_job where r_userinfo.userid=r_resume.userid  and r_job.jobid=r_resume.jobid and r_job.companyid=? and r_job.jobid=?  and auditstate like ? and createtime > ? and createtime < ? ";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);
            ps.setString(3, "%"+keyState+"%");
            ps.setString(4, keyCreateTime);
            ps.setString(5, keyEndTime);
            ps.setString(1, companyId);
            ps.setString(2, jobId);

        }
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            return rs.getInt("sum");
        }
        return 0;
    }

    @Override
    public int countDeliverdResume(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String keyCreateTime = req.getParameter("keyCreateTime");
        String keyEndTime = req.getParameter("keyEndTime");
        String keyState = req.getParameter("keyState");
        String userId = req.getParameter("userId");
        String companyName = req.getParameter("companyName");
        String jobName = req.getParameter("jobName");

        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyState)
                && StringUtils.isNullOrEmpty(keyCreateTime) && StringUtils.isNullOrEmpty(keyEndTime)
                && StringUtils.isNullOrEmpty(companyName)  && StringUtils.isNullOrEmpty(jobName)) {
            sql = "SELECT count(*) as sum from r_company,r_resume,r_job where r_job.jobid=r_resume.jobid and r_resume.userid=? and r_company.companyid=r_job.companyid";
            ps = conn.prepareStatement(sql);
            ps.setString(1,userId);
            System.out.println("sql = " + sql);
        } else if (
                !StringUtils.isNullOrEmpty(keyState)||
                        !StringUtils.isNullOrEmpty(keyCreateTime)||
                        !StringUtils.isNullOrEmpty(keyEndTime) ||
                        !StringUtils.isNullOrEmpty(companyName) ||
                        !StringUtils.isNullOrEmpty(jobName)
        ) {
            sql = "SELECT count(*) as sum from r_company,r_resume,r_job where r_job.jobid=r_resume.jobid and r_resume.userid=? and r_company.companyid=r_job.companyid  and auditstate like ?  and r_resume.createtime >=?  and r_resume.createtime  <= ? and r_company.name like ? and r_job.jobname like ? ";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);
            ps.setString(1,userId);
//            ps.setString(2,jobId);
            ps.setString(2, "%"+keyState+"%");
            ps.setString(5, "%"+companyName+"%");
            ps.setString(6, "%"+jobName+"%");
            ps.setString(3, keyCreateTime);
            ps.setString(4, keyEndTime);

        }
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            return rs.getInt("sum");
        }
        return 0;
    }

    @Override
    public int countReadResumeByJobId(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String jobId = req.getParameter("jobId");

        String sql = "";
        PreparedStatement ps = null;

        sql = "SELECT count(*) as sum from r_resume where jobid=?  and readstate=0";
        ps = conn.prepareStatement(sql);
        ps.setString(1,jobId);
        System.out.println("sql = " + sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            return rs.getInt("sum");
        }
        return 0;
    }

    @Override
    public int checkedeReadResumeByJobId(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String jobId = req.getParameter("jobId");
        String auditId = req.getParameter("auditId");
        String sql = "";
        PreparedStatement ps = null;

        sql = "update r_resume set readstate=1 where jobid=?  and auditId=?";
        ps = conn.prepareStatement(sql);
        ps.setString(1,jobId);
        ps.setInt(2, Integer.parseInt(auditId));
        System.out.println("sql = " + sql);
        int i = ps.executeUpdate();
        while(i==1){
            return 1;
        }
        return 0;
    }


    @Override
    public int addPointReadResume(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String jobId = req.getParameter("jobId");
        String auditId = req.getParameter("auditId");

        String sql = "";
        PreparedStatement ps = null;

        sql = "SELECT readstate from r_resume where jobid=?  and auditId=?";
        ps = conn.prepareStatement(sql);
        ps.setString(1,jobId);
        ps.setInt(2, Integer.parseInt(auditId));
        System.out.println("sql = " + sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            return Integer.parseInt(rs.getString("readstate"));
        }
        return -1;
    }

    @Override
    public int RemoveResume(Resume resume) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String sql = "DELETE from r_resume where auditid=?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, resume.getAuditId());
//            ps.setString(2, resume.getUserId());
            int i = ps.executeUpdate();
            if (i == 1) {
                return 1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

}
